package com.example.sens.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.util.HSSFColor;
//import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.Date;
import java.util.List;

/**
 *导出Excel表格工具
 */
@Slf4j
public class ExportExcelUtils {

    public static Response<String> excelPath(List<Object[]> dataList, String[] rowName, String title, String[] layout){
        if (dataList.size() > 10000) {
            throw new Error("导出失败，请确保导出的数据在1万条以内！");
        }
        // 延迟解析比率
//        ZipSecureFile.setMinInflateRatio(-1.0d);
        Response<String> result=new Response<>();
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        // sheet名称
        SXSSFSheet sxssfSheet;
        if(title.length() >6 && title.substring(title.length()-6).equals("案件汇总一览")){
            sxssfSheet = (SXSSFSheet) workbook.createSheet(title.substring(title.length()-9));
        }else{
            sxssfSheet = (SXSSFSheet) workbook.createSheet(title);
        }
        //存放表格每一列中内容最多的字符串长度
        int[] maxArray = new int[rowName.length];
        for (int row = 0; row < rowName.length; row ++){
            int min = 5;  //单元格最小宽度
            int max = 20; //单元格最大宽度
            for (int column = 0 ; column < dataList.size(); column ++){
                Object[] obj = dataList.get(column);
                if (obj[row] != null && min < obj[row].toString().length()){
                    min = obj[row].toString().length();
                }
                if (obj[row] != null && obj[row].toString().length() > max){
                    min = max;
                }
            }
            if (min < rowName[row].length()){
                min = rowName[row].length();
            }
            maxArray[row]=min;
        }
        //手动设置根据单元格内容自适应列宽
//        sxssfSheet.trackAllColumnsForAutoSizing();
        for (int i = 0; i < rowName.length; i++) {    //以内容最宽的长度设置每一列的宽度
            sxssfSheet.autoSizeColumn((short)i);
            int length = maxArray[i];
            sxssfSheet.setColumnWidth(i, (short)(length * 256 * 2));
        }
        //sheet样式定义
        CellStyle columnTopStyle = ExportExcelUtils.getColumnTopStyle(workbook, 18); // 头样式
        CellStyle columnStyle = ExportExcelUtils.getColumnStyle(workbook, 11); // 标题样式
        //表格标题
        sxssfSheet.addMergedRegion(new CellRangeAddress(0,0,0,(rowName.length-1)));
        // 合并第一行的所有列
        Row rowm = sxssfSheet.createRow(0);
        // 行
        rowm.setHeightInPoints(31f);
        Cell cellTiltle = rowm.createCell(0);
        // 单元格
        cellTiltle.setCellStyle(columnTopStyle);
        cellTiltle.setCellValue(title);
        int columnNum = rowName.length;
        Row rowRowName = sxssfSheet.createRow(1);
        rowRowName.setHeightInPoints(21f);
        CellStyle cells = workbook.createCellStyle();
        cells.setBottomBorderColor(HSSFColor.BLACK.index);
        rowRowName.setRowStyle(cells);
        // 设置样式一
        for (int i = 0; i < columnNum; i++) {
            Cell cellRowName = rowRowName.createCell(i);
            cellRowName.setCellType(SXSSFCell.CELL_TYPE_STRING);
            XSSFRichTextString text = new XSSFRichTextString(rowName[i]);
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(columnStyle);
        }
        CellStyle style = getStyle(workbook, 11);
        for (int i = 0; i < dataList.size(); i++) {
            Object[] obj = dataList.get(i);//遍历每个对象
            Row row = sxssfSheet.createRow(i + 2);//创建所需的行数
            row.setHeightInPoints(17.25f);
            for (int j = 0; j < obj.length; j++) {
                if (layout[j].equals("center")){
                    style.setAlignment(CellStyle.ALIGN_CENTER);
                }else {
                    style.setAlignment(CellStyle.ALIGN_LEFT);
                }
                Cell cell = null;   //设置单元格的数据类型
                cell = row.createCell(j, SXSSFCell.CELL_TYPE_STRING);
                if (!"".equals(obj[j]) && obj[j] != null) {
                    cell.setCellValue(obj[j].toString());  //设置单元格的值
                } else {
                    cell.setCellValue("  ");
                }
                cell.setCellStyle(style); // 设置样式
            }
        }

        if(title.length() >6 && title.substring(title.length()-6).equals("案件汇总一览")){
            // 合并单元格
            sxssfSheet.addMergedRegion(new CellRangeAddress(dataList.size(),dataList.size()+1,0,0));
            Row titleRow = sxssfSheet.getRow(dataList.size());
            for (int i = 0; i < columnNum; i++) {
                Cell cellRowName = titleRow.createCell(i);
                XSSFRichTextString text = new XSSFRichTextString(dataList.get(dataList.size()-2)[i].toString());
                cellRowName.setCellType(SXSSFCell.CELL_TYPE_STRING);
                cellRowName.setCellValue(text);
                cellRowName.setCellStyle(columnStyle);
            }
        }
        if (workbook != null) {
            String path = ExportExcelUtils.distributePath("temp");
            String csvTempFile = path + title+ System.currentTimeMillis() + ".xlsx";
            java.io.File file = new java.io.File(csvTempFile);
            try {
                FileOutputStream fileOutputStream = new FileOutputStream(file);
                workbook.write(fileOutputStream);//将数据写出
            } catch (IOException e) {
                e.printStackTrace();
                throw new Error("文件导出失败");
            }
            result.setData(csvTempFile);
        }
        return result;
    }

    public static CellStyle getColumnTopStyle(SXSSFWorkbook workbook, int fontSize) {          // 设置字体
        Font font = workbook.createFont();        //设置字体大小
        font.setFontHeightInPoints((short) fontSize);        //字体加粗
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);        //设置字体名字
        font.setFontName("宋体");        //设置样式;
        CellStyle style = workbook.createCellStyle();        //在样式用应用设置的字体;
        style.setFont(font);        //设置自动换行;
        style.setWrapText(false);        //设置水平对齐的样式为居中对齐;
        style.setAlignment(CellStyle.ALIGN_CENTER);        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        return style;
    }

    public static CellStyle getColumnStyle(SXSSFWorkbook workbook, int fontSize) {          // 设置字体
        Font font = workbook.createFont();        //设置字体大小
        font.setFontHeightInPoints((short) fontSize);        //字体加粗
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);        //设置字体名字
        font.setFontName("宋体");        //设置样式;
        CellStyle style = workbook.createCellStyle();        //设置底边框;
        style.setBorderBottom(CellStyle.BORDER_THIN);        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);        //设置左边框;
        style.setBorderLeft(CellStyle.BORDER_THIN);        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);        //设置右边框;
        style.setBorderRight(CellStyle.BORDER_THIN);        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);        //设置顶边框;
        style.setBorderTop(CellStyle.BORDER_THIN);        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);        //在样式用应用设置的字体;
        style.setFont(font);        //设置自动换行;
        style.setWrapText(false);        //设置水平对齐的样式为居中对齐;
        style.setAlignment(CellStyle.ALIGN_CENTER);        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);                //设置背景填充色（前景色）
        style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);//设置别的颜色请去网上查询相关文档
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        return style;
    }

    public static CellStyle getStyle(SXSSFWorkbook workbook, int fontSize) {        //设置字体
        Font font = workbook.createFont();        //设置字体大小
        font.setFontHeightInPoints((short) fontSize);        //字体加粗
        font.setFontName("宋体");        //设置样式;
        CellStyle style = workbook.createCellStyle();        //设置底边框;
        style.setBorderBottom(CellStyle.BORDER_THIN);        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);        //设置左边框;
        style.setBorderLeft(CellStyle.BORDER_THIN);        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);        //设置右边框;
        style.setBorderRight(CellStyle.BORDER_THIN);        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);        //设置顶边框;
        style.setBorderTop(CellStyle.BORDER_THIN);        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);        //在样式用应用设置的字体;
        style.setFont(font);        //设置自动换行;
        style.setWrapText(true);        //设置水平对齐的样式为居中对齐;
        style.setAlignment(CellStyle.ALIGN_CENTER);        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        return style;
    }

    public static String distributePath(String keyPath) {
        StorageProperties properties = new StorageProperties();
        String basePath = properties.getLocation();
        // 根据不同文件文件类型-不同日期生成不同文件夹
        Date date = new Date();
        String datePath = DateUtils.DateFormatToString(date, "yyyy") + File.separator +
                DateUtils.DateFormatToString(date, "MM") + File.separator +
                DateUtils.DateFormatToString(date, "dd");
        Path path;
        try {
            path = Paths.get(basePath, keyPath, datePath);
            Files.createDirectories(path);
        } catch (IOException e) {
            throw new Error("Failed to store file path", e);
        }
        return path.toString() + File.separator;
    }

}
